package com.qjcc.util;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class OracleDataSourceFactory {
    private static Logger log = Logger.getLogger(OracleDataSourceFactory.class);
    private static BasicDataSource bs = null;
     
    
    public static String driver="oracle.jdbc.driver.OracleDriver";
//    public static String url="jdbc:oracle:thin:@localhost:1521:ORCL";
    public static String url="jdbc:oracle:thin:@222.210.108.226:1521:orcl";
    public static String  userName="qjcc";
    public static String password="qjcc";
    
    boolean isDebug = false;

    private volatile static OracleDataSourceFactory oracleDataSourceFactory;

    private OracleDataSourceFactory(){};

    public static OracleDataSourceFactory getInstance(){
        if(oracleDataSourceFactory == null){
            synchronized (OracleDataSourceFactory.class) {
                if(oracleDataSourceFactory == null){
                    oracleDataSourceFactory = new OracleDataSourceFactory();
                }
            }
        }
        return oracleDataSourceFactory;
    }
    
    public static void main(String[] argv)throws Exception{
    	OracleDataSourceFactory sdsf = new OracleDataSourceFactory();
    	
    	List<Map<String, Object>> objList = sdsf.getTouyun();

    	//java.io.ObjectOutputStream oos = new java.io.ObjectOutputStream(new java.io.FileOutputStream("c:/1.out"));

    	//oos.writeObject(objList);

    	//oos.flush();

    	//oos.close();

    	System.out.println(objList.size());


    }



    /**
     * 根据类型查询需要同步的数据
     * */
    public List<Map<String , Object>>  getTouyun()throws Exception{

    	List<Map<String,Object>> refList = null;

    	if(isDebug){
	    	/*******************************************************************************************************************/
	    	// 测试环境代码 begin
	    	java.io.ObjectInputStream ois = new java.io.ObjectInputStream(new java.io.FileInputStream("/Users/niejiao/custom/work/千嘉科技/呼叫中心/test.out"));
	    	refList = (List<Map<String, Object>>) ois.readObject();
	    	if(1 < 2)
	    		return refList;
	    	// 测试环境代码 end
	    	/*******************************************************************************************************************/
    	}

    	/*
    	String sql = "SELECT TOP 10 NY, MY_KEHU, MY_TOUYUN_ZONGSHU , MY_CHAOBIAO_SHU , MY_CHAOBIAO_CHENGGONG_SHU , SY_KEHU , SY_TOUYUN_ZONGSHU , "+
                " SY_CHAOBIAO_SHU , SY_CHAOBIAO_CHENGGONG_SHU , XXH_KEHU , XXH_YONGHU ,  XXH_GIS , XXH_GIS_CHANGDU , " +
                " XXH_GIS_SHEBEISHU , XXH_QIANGXIAN , XXH_QIANGXIAN_CISHU , XXH_SCADA , XXH_SCADA_XITONG , " +
                " XXH_SCADA_MENZHAN , XXH_SCADA_GUANWANG  XXH_SCADA_TIAOYA FROM QJ_TOUYUN ORDER BY NY DESC";
    	*/
		String sql = "select sum(cnt1) CNT1, sum(cnt2) CNT2, sum(cnt3) CNT3, sum(cnt4) CNT4 from(";
		sql += " select ";
		sql += " COUNT( DECODE(TO_CHAR(a.SEND_TIME,'YYYY-MM') , TO_CHAR(SYSDATE , 'YYYY-MM') , 1 , NULL) ) CNT1,";
		sql += " COUNT( DECODE(TO_CHAR(a.CLOSE_TIME,'YYYY-MM') , TO_CHAR(SYSDATE , 'YYYY-MM') , 1 , NULL) ) CNT2,";
		sql += " COUNT( DECODE(a.status , '1' , 1 , NULL) ) CNT3 ,";
		sql += " 0 CNT4";
		sql += " from CC_WORK_CARD a";
		sql += " union all ";
		sql += " select 0 CNT1 , 0 CNT2 , 0 CNT3 ,";
		sql += " COUNT( DECODE(TO_CHAR(a.create_date,'YYYY-MM') , TO_CHAR(SYSDATE , 'YYYY-MM') , 1 , NULL) ) CNT4 ";
		sql += " from cc_sign  a";
		sql += " )";


    	Connection con = OracleDataSourceFactory.getConnection();
    	ResultSet rs = null;
    	PreparedStatement ps = null;
    	try{
    		
    		ps = con.prepareStatement(sql);
    		
    		rs = ps.executeQuery();
    		
    		// 构建返回数据
    		MapListHandler mlh = new MapListHandler();
    		
    		refList = mlh.handle(rs);
    		
    		
    	}finally{
    		OracleDataSourceFactory.closeCon(rs, ps, con);
    	}
    	
    	
    	return refList;
    }
    
    /**
     * 创建数据源
     * @return
     */
    public static BasicDataSource getDataSource() throws Exception{
        if(bs==null){
            log.info("数据库连接信息：[driver:"+driver+",url:"+url+",userName:"+userName+",password:"+password+"]");
            bs = new BasicDataSource();
            bs.setDriverClassName(driver);
            bs.setUrl(url);
            bs.setUsername(userName);
            bs.setPassword(password);
            bs.setMaxActive(200);//设置最大并发数
            bs.setInitialSize(5);//数据库初始化时，创建的连接个数
            bs.setMinIdle(5);//最小空闲连接数
            bs.setMaxIdle(200);//数据库最大连接数
            bs.setMaxWait(1000);
            bs.setMinEvictableIdleTimeMillis(60*1000);//空闲连接60秒中后释放
            bs.setTimeBetweenEvictionRunsMillis(5*60*1000);//5分钟检测一次是否有死掉的线程
            bs.setTestOnBorrow(true);
        }
        return bs;
    }
     
    /**
     * 释放数据源
     */
    public static void shutDownDataSource() throws Exception{
        if(bs!=null){
            bs.close();
        }
    }
     
    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection con=null;
        try {
            if(bs!=null){
                con=bs.getConnection();
            }else{
                con=getDataSource().getConnection();
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return con;
    }
     
    /**
     * 关闭连接
     */
    public static void closeCon(ResultSet rs,PreparedStatement ps,Connection con){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                log.error("关闭结果集ResultSet异常！"+e.getMessage(), e);
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (Exception e) {
                log.error("预编译SQL语句对象PreparedStatement关闭异常！"+e.getMessage(), e);
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (Exception e) {
                log.error("关闭连接对象Connection异常！"+e.getMessage(), e);
            }
        }
    }
}
